﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SCRUM.Core.BusinessObjects;
using System.Data.Common;
using SCRUM.Core.DAO;
using System.Collections.Specialized;
using SCRUM.Core.Exceptions.Database;
using System.Data.SqlClient;

namespace SCRUM.Core.DataProviders
{
    /// <summary>
    /// Privider for comunication with the database for backlog item
    /// </summary>
    public partial class DBDataProvider : DataProvider
    {
        /// <summary>
        /// Creates new Bli.
        /// </summary>
        /// <param name="bli">The specific Bli.</param>
        /// <param name="userStoryID">User story id to add Bli to.</param>
        /// <returns>Bli with ID</returns>
        public override BLI CreateBli(BLI bli, int userStoryID)
        {
            int bliID = InsertBli(bli, userStoryID);
            return GetBliByID(bliID);
        }

        /// <summary>
        /// Inserts the bli into database.
        /// </summary>
        /// <param name="bli">The bli.</param>
        /// <param name="userStoryID">The user story ID.</param>
        /// <returns>identifier of newly created bli</returns>
        private int InsertBli(BLI bli, int userStoryID)
        {
            try
            {
                DbParameter dbName = CreateParameter("name", bli.Name);
                DbParameter dbTypeID = CreateParameter("typeID", bli.TypeID);
                DbParameter dbDesc = CreateParameter("description", bli.Description);
                DbParameter dbState = CreateParameter("bliStateId", bli.State.ID);
                DbParameter dbuserStoryID = CreateParameter("userStoryID", userStoryID);
                DbParameter dbestimation = CreateParameter("estimation", bli.Estimation);
                //DbParameter dbProgress = CreateParameter("progress", bli.Progress);
                DbParameter dbpriority = CreateParameter("priority", bli.Priority);

                connection.Open();
                int data = ExecuteNonQuery("bli/CreateBli", dbName, dbTypeID, dbDesc, dbuserStoryID, dbState, dbpriority, dbestimation);//, dbProgress);
                if (data != 1)
                {
                    throw new InconsistentDatabaseException("BliNotCreated");
                }
                return Convert.ToInt32(GetScalar("bli/GetLastInsertedBliID"));
            }
            catch (SqlException)
            {
                throw new InconsistentDatabaseException("BliNotCreated");
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// Updates the Bli.
        /// </summary>
        /// <param name="bli">The specific Bli.</param>
        /// <param name="userStoryID">id of user story that Bli is in</param>
        public override void UpdateBli(BLI bli, int userStoryID)
        {
            DbParameter dbID = CreateParameter("bliID", bli.ID);
            DbParameter dbName = CreateParameter("name", bli.Name);
            DbParameter dbTypeID = CreateParameter("typeID", bli.TypeID);
            DbParameter dbDesc = CreateParameter("description", bli.Description);
            DbParameter dbState = CreateParameter("stateId", bli.State.ID);
            DbParameter dbuserStoryID = CreateParameter("userStoryID", userStoryID);
            DbParameter dbestimation = CreateParameter("estimation", bli.Estimation);
            //DbParameter dbProgress = CreateParameter("progress", bli.Progress);
            DbParameter dbpriority = CreateParameter("priority", bli.Priority);
            try
            {
                connection.Open();
                int data = ExecuteNonQuery("bli/UpdateBli", dbID, dbName, dbTypeID, dbDesc, dbuserStoryID, dbState, dbestimation, dbpriority);//dbProgress, );
                if (data != 1)
                {
                    throw new InconsistentDatabaseException("BliToUpdateNotFound");
                }
            }
            catch (SqlException)
            {
                throw new InconsistentDatabaseException("BliCouldNotBeDeleted");
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// Deletes the Bli.
        /// </summary>
        /// <param name="bliId">The specific Bli id.</param>
        public override void DeleteBli(int bliId)
        {
            DbParameter dbID = CreateParameter("bliID", bliId);

            try
            {
                connection.Open();
                int data = ExecuteNonQuery("bli/DeleteBli", dbID);
                if (data != 1)
                {
                    throw new Exception("BliToDeleteNotFound");
                }
            }
            catch (SqlException)
            {
                throw new InconsistentDatabaseException("BliCouldNotBeDeleted");
            }
            finally
            {
                connection.Close();
            }
        }

        /// <summary>
        /// Gets the bli from Dbdatareader
        /// </summary>
        /// <param name="data">The data.</param>
        /// <returns></returns>
        protected BLI GetBli(DbDataReader data)
        {
            return new BLI(
                (int)data["ID"],
                (string)data["NAME"],
                (int)data["BLI_TYPE_ID"],
                (string)data["DESCRIPTION"],
                (int)data["USER_STORY_ID"],
                GetIntByColumn(data, "STATE_ID"),
                GetDoubleByColumn(data, "ESTIMATION"),
                GetDoubleByColumn(data, "PROGRESS"),
                (int)data["PRIORITY"],
                GetDateByColumn(data, "CHANGED")
            );
        }

        /// <summary>
        /// Common method for retrieving the blis with all specified parameters
        /// </summary>
        /// <param name="bliID">The bli ID.</param>
        /// <param name="userStoryID">The user story ID.</param>
        /// <param name="releaseID">The release ID.</param>
        /// <param name="sprintID">The sprint ID.</param>
        /// <param name="projectID">The project ID.</param>
        /// <returns></returns>
        public override IList<BLI> GetBlis(int projectID, int releaseID, int sprintID, int userStoryID, int bliID, int taskID, int stateID, string name)
        {


            IList<BLI> blis = new List<BLI>();
            try
            {
                DbParameter dbBliID = CreateParameter("bliID", bliID);
                DbParameter dbUserStoryID = CreateParameter("userStoryID", userStoryID);
                DbParameter dbReleaseID = CreateParameter("releaseID", releaseID);
                DbParameter dbSprintID = CreateParameter("sprintID", sprintID);
                DbParameter dbProjectID = CreateParameter("projectID", projectID);
                DbParameter dbTaskID = CreateParameter("taskID", taskID);
                DbParameter dbStateID = CreateParameter("stateID", stateID);
                DbParameter dbName = CreateParameter("name", name);
                

                connection.Open();

                DbDataReader data = GetGrid("bli/GetBlis", dbBliID, dbUserStoryID, dbReleaseID, dbSprintID, dbProjectID, dbTaskID, dbStateID, dbName);
                while (data.Read())
                {
                    BLI bli = GetBli(data);
                    blis.Add(bli);
                }
            }
            finally
            {
                connection.Close();
            }
            return blis;
        }

        /// <summary>
        /// Get all BLIs.
        /// </summary>
        public override IList<BLI> GetAllBlis()
        {
            return GetBlis(-1, -1, -1, -1, -1, -1, -1, "");
        }        

        /// <summary>
        /// Get all BLIs for specific project.
        /// </summary>
        /// <param name="projectID">Id of project</param>
        public override IList<BLI> GetAllBlisForProject(int projectID)
        {
            return GetBlis(projectID, -1, -1, -1, -1, -1, -1, "");
        }

        /// <summary>
        /// Get all BLIs for specific release.
        /// </summary>
        /// <param name="releaseID">Id of release</param>
        public override IList<BLI> GetAllBlisForRelease(int releaseID)
        {
            return GetBlis(-1, releaseID, -1, -1, -1, -1, -1, "");
        }

        /// <summary>
        /// Get all BLIs for specific sprint.
        /// </summary>
        /// <param name="sprintID">Id of sprint</param>
        public override IList<BLI> GetAllBlisForSprint(int sprintID)
        {
            return GetBlis(-1, -1, sprintID, -1, -1, -1, -1, "");
        }

        /// <summary>
        /// Get all BLIs for specific user story.
        /// </summary>
        /// <param name="userStoryID">Id of user story</param>
        public override IList<BLI> GetAllBlisForUserStory(int userStoryID)
        {
            return GetBlis(-1, -1, -1, userStoryID, -1, -1, -1, "");
        }

        /// <summary>
        ///  Returns BLI according to id
        /// </summary>
        /// <param name="bliID">id of BLI</param>
        /// <returns>BLI details</returns>
        public override BLI GetBliByID(int bliID)
        {
            IList<BLI> blis = GetBlis(-1, -1, -1, -1, bliID, -1, -1, "");
            if (blis.Count != 1)
            {
                throw new InconsistentDatabaseException("BliNotFound");
            }
            return blis[0];
        }

        /// <summary>
        /// Returns BLI according to task 
        /// </summary>
        /// <param name="task">Task to search BLI for</param>
        /// <returns>BLI</returns>
        public override BLI GetBliByTask(Task task)
        {
            IList<BLI> blis = GetBlis(-1, -1, -1, -1, -1, task.ID, -1, "");
            if (blis.Count != 1)
            {
                throw new InconsistentDatabaseException("BliNotFound");
            }
            return blis[0];
        }

        /// <summary>
        /// Gets all types of Bli
        /// </summary>
        /// <returns>list of Bli types</returns>
        public override NameValueCollection GetBliTypes()
        {
            NameValueCollection types = new NameValueCollection();

            try
            {
                connection.Open();
                DbDataReader data = GetGrid("bli/GetBliTypes");
                while (data.Read())
                {
                    int ID = (int)data["ID"];
                    String s = (String)data["NAME"];
                    types.Add(s, ID.ToString());
                }
            }
            finally
            {
                connection.Close();
            }
            return types;
        }
    }
}
